package com.ht.shortlink.project.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ht.shortlink.project.dao.entity.LinkAccessLogsDO;
import com.ht.shortlink.project.dao.entity.LinkAccessStatsDO;
import com.ht.shortlink.project.dto.req.ShortLinkStatsGroupReqDTO;
import com.ht.shortlink.project.dto.req.ShortLinkStatsReqDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.HashMap;
import java.util.List;

public interface LinkAccessLogsMapper extends BaseMapper<LinkAccessLogsDO> {
    /**
     *  根据短链接查询pv、uv、uip
     */
    @Select("SELECT COUNT(user) AS pv,COUNT(DISTINCT user) AS uv,COUNT(DISTINCT ip) AS uip FROM t_link_access_logs " +
            "WHERE full_short_url=#{param.fullShortUrl} " +
            "AND gid=#{param.gid} " +
            "AND create_time BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY full_short_url,gid;")
    LinkAccessStatsDO findPvUvUipByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);

    /**
     *  根据分组查询pv、uv、uip
     */
    @Select("SELECT COUNT(user) AS pv,COUNT(DISTINCT user) AS uv,COUNT(DISTINCT ip) AS uip FROM t_link_access_logs " +
            "WHERE gid=#{param.gid} " +
            "AND create_time BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY gid;")
    LinkAccessStatsDO findPvUvUipByGroup(@Param("param") ShortLinkStatsGroupReqDTO requestParam);

    /**
     *  根据短链接查询 Top 5 IP
     */
    @Select("SELECT ip,COUNT(ip) AS count FROM t_link_access_logs " +
            "WHERE full_short_url=#{param.fullShortUrl} AND gid=#{param.gid} AND create_time BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY full_short_url,gid,ip " +
            "ORDER BY count DESC " +
            "LIMIT 5;")
    List<HashMap<String, Object>> findTopIPByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);

    /**
     *  根据分组查询 Top 5 IP
     */
    @Select("SELECT ip,COUNT(ip) AS count FROM t_link_access_logs " +
            "WHERE gid=#{param.gid} AND create_time BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY gid,ip " +
            "ORDER BY count DESC " +
            "LIMIT 5;")
    List<HashMap<String, Object>> findTopIPByGroup(@Param("param") ShortLinkStatsGroupReqDTO requestParam);

    /**
     * 根据短链接获取指定日期内新旧访客数据
     */
    @Select("SELECT " +
            "    SUM(old_user) AS oldUserCnt, " +
            "    SUM(new_user) AS newUserCnt " +
            "FROM ( " +
            "    SELECT " +
            "        CASE WHEN COUNT(DISTINCT DATE(create_time)) > 1 THEN 1 ELSE 0 END AS old_user, " +
            "        CASE WHEN COUNT(DISTINCT DATE(create_time)) = 1 AND MAX(create_time) >= #{param.startDate} AND MAX(create_time) <= #{param.endDate} THEN 1 ELSE 0 END AS new_user " +
            "    FROM " +
            "        t_link_access_logs " +
            "    WHERE " +
            "        full_short_url = #{param.fullShortUrl} " +
            "        AND gid = #{param.gid} " +
            "    GROUP BY " +
            "        user " +
            ") AS user_counts;")
    HashMap<String, Object> findUvTypeCntByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);

    /**
     * 根据分组获取指定日期内新旧访客数据
     */
    @Select("SELECT " +
            "    SUM(old_user) AS oldUserCnt, " +
            "    SUM(new_user) AS newUserCnt " +
            "FROM ( " +
            "    SELECT " +
            "        CASE WHEN COUNT(DISTINCT DATE(create_time)) > 1 THEN 1 ELSE 0 END AS old_user, " +
            "        CASE WHEN COUNT(DISTINCT DATE(create_time)) = 1 AND MAX(create_time) >= #{param.startDate} AND MAX(create_time) <= #{param.endDate} THEN 1 ELSE 0 END AS new_user " +
            "    FROM " +
            "        t_link_access_logs " +
            "    WHERE " +
            "        gid = #{param.gid} " +
            "    GROUP BY " +
            "        user " +
            ") AS user_counts;")
    HashMap<String, Object> findUvTypeCntByGroup(@Param("param") ShortLinkStatsGroupReqDTO requestParam);

    @Select("<script> " +
            "SELECT user, " +
            "CASE WHEN MIN(create_time) BETWEEN #{startDate} AND #{endDate} THEN '新访客' ELSE '老访客' END AS uvType " +
            "FROM t_link_access_logs " +
            "WHERE full_short_url = #{fullShortUrl} " +
            "AND gid = #{gid} " +
            "AND user IN " +
            "<foreach item='item' index='index' collection='userList' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "GROUP BY user " +
            "</script>")
    List<HashMap<String, Object>> queryUvType(@Param("fullShortUrl") String fullShortUrl,
                                              @Param("gid") String gid,
                                              @Param("startDate") String startDate,
                                              @Param("endDate") String endDate,
                                              @Param("userList") List<String> userList);

    @Select("<script> " +
            "SELECT user, " +
            "CASE WHEN MIN(create_time) BETWEEN #{startDate} AND #{endDate} THEN '新访客' ELSE '老访客' END AS uvType " +
            "FROM t_link_access_logs " +
            "WHERE gid = #{gid} " +
            "AND user IN " +
            "<foreach item='item' index='index' collection='userList' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "GROUP BY user " +
            "</script>")
    List<HashMap<String, Object>> queryGroupUvType(@Param("gid") String gid,
                                                   @Param("startDate") String startDate,
                                                   @Param("endDate") String endDate,
                                                   @Param("userList") List<String> userList);
}
